{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b0387335",
   "metadata": {},
   "source": [
    "### 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "id": "aa93c142",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "id": "7e5e7282",
   "metadata": {},
   "outputs": [],
   "source": [
    "male_table = pd.read_excel('./18级高一体测成绩汇总.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "id": "ea51d3d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "female_table = pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "8c916d5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "score = pd.read_excel('./体侧成绩评分表.xls',header = [0,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e92971c8",
   "metadata": {},
   "source": [
    "### 转换分秒形式为分钟"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "4407bfc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_sec=male_table['男1000米跑'].str.extract(r'(\\d+)\\'(\\d+)') .applymap(lambda x : float(x))        #用正则表达式提取分、秒"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "055bf0b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "male_table['男1000米跑'] = (min_sec[0]+min_sec[1]/60).round(2)      # 分、秒转换成分钟（float)，改写原表数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "7bc028aa",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      4.22\n",
       "1      4.27\n",
       "2      4.15\n",
       "3      4.35\n",
       "4      3.73\n",
       "       ... \n",
       "472    4.38\n",
       "473    5.32\n",
       "474    3.42\n",
       "475    4.65\n",
       "476     NaN\n",
       "Name: 男1000米跑, Length: 477, dtype: float64"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_table['男1000米跑']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "953664fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_sec2 = score['男1000米跑']['成绩'].str.extract(r'(\\d+)\\'(\\d+)').applymap(lambda x : float(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "79938e0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_sec3=score['女800米跑']['成绩'].str.extract(r'(\\d+)\\'(\\d+)') .applymap(lambda x : float(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "02f32eac",
   "metadata": {},
   "outputs": [],
   "source": [
    "score.loc[:,('男1000米跑','成绩')] = (min_sec2[0]+min_sec2[1]/60).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "e00f6b54",
   "metadata": {},
   "outputs": [],
   "source": [
    "score.loc[:,('女800米跑','成绩')] = (min_sec3[0]+min_sec3[1]/60).round(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4525af92",
   "metadata": {},
   "source": [
    "### 全部数值数据转换为float类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "a7bb0a9c",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\AppData\\Local\\Temp/ipykernel_3684/1304519191.py:1: DeprecationWarning: `np.float` is a deprecated alias for the builtin `float`. To silence this warning, use `float` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.float64` here.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  score = score.astype(np.float).round(2)     #评分标准表 全部数据转为 float类型\n"
     ]
    }
   ],
   "source": [
    "score = score.astype(np.float).round(2)     #评分标准表 全部数据转为 float类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "c2db9d54",
   "metadata": {},
   "outputs": [],
   "source": [
    "score.fillna(0,inplace=True)   #填充空数据为 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "749bc5e6",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "男肺活量     成绩    float64\n",
       "         分数    float64\n",
       "女肺活量     成绩    float64\n",
       "         分数    float64\n",
       "男50米跑    成绩    float64\n",
       "         分数    float64\n",
       "女50米跑    成绩    float64\n",
       "         分数    float64\n",
       "男体前屈     成绩    float64\n",
       "         分数    float64\n",
       "女体前屈     成绩    float64\n",
       "         分数    float64\n",
       "男跳远      成绩    float64\n",
       "         分数    float64\n",
       "女跳远      成绩    float64\n",
       "         分数    float64\n",
       "男引体      成绩    float64\n",
       "         分数    float64\n",
       "女仰卧      成绩    float64\n",
       "         分数    float64\n",
       "男1000米跑  成绩    float64\n",
       "         分数    float64\n",
       "女800米跑   成绩    float64\n",
       "         分数    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "id": "8a8199c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "score.to_excel('./score.xlsx',header=True)       #整理后的评分标准表 导出为excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "id": "4bff8c30",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\AppData\\Local\\Temp/ipykernel_3684/2489809174.py:1: DeprecationWarning: `np.float` is a deprecated alias for the builtin `float`. To silence this warning, use `float` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.float64` here.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  male_table.loc[:,\"男1000米跑\":] = male_table.loc[:,\"男1000米跑\":].astype(np.float).round(2) #男体测成绩表数值全部转成 float类型\n"
     ]
    }
   ],
   "source": [
    "male_table.loc[:,\"男1000米跑\":] = male_table.loc[:,\"男1000米跑\":].astype(np.float).round(2) #男体测成绩表数值全部转成 float类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "90f52b12",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "班级           int64\n",
       "性别          object\n",
       "男1000米跑    float64\n",
       "男50米跑      float64\n",
       "男跳远        float64\n",
       "男体前屈       float64\n",
       "男引体        float64\n",
       "男肺活量       float64\n",
       "身高         float64\n",
       "体重         float64\n",
       "BMI        float64\n",
       "男50米跑分数    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_table.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "143914ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "male_table.fillna(0,inplace=True)   #填充空数据为 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "9cb839f0",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "班级         False\n",
       "性别         False\n",
       "男1000米跑    False\n",
       "男50米跑      False\n",
       "男跳远        False\n",
       "男体前屈       False\n",
       "男引体        False\n",
       "男肺活量       False\n",
       "身高         False\n",
       "体重         False\n",
       "BMI        False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_table.isnull().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "id": "ec8eeea9",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\User\\AppData\\Local\\Temp/ipykernel_3684/2751357782.py:1: DeprecationWarning: `np.float` is a deprecated alias for the builtin `float`. To silence this warning, use `float` by itself. Doing this will not modify any behavior and is safe. If you specifically wanted the numpy scalar type, use `np.float64` here.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  female_table.loc[:,\"女800米跑\":] = female_table.loc[:,\"女800米跑\":].astype(np.float).round(2)   #女体测成绩表数值全部转成 float类型\n"
     ]
    }
   ],
   "source": [
    "female_table.loc[:,\"女800米跑\":] = female_table.loc[:,\"女800米跑\":].astype(np.float).round(2)   #女体测成绩表数值全部转成 float类型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "6a469822",
   "metadata": {},
   "outputs": [],
   "source": [
    "female_table.fillna(0,inplace=True)   #填充空数据为 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "418f9050",
   "metadata": {},
   "source": [
    "#### 成绩表增加分数列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "id": "6e545f54",
   "metadata": {},
   "outputs": [],
   "source": [
    "#跑步类项目成绩对应出分数 ——男\n",
    "sports = ['男50米跑','男1000米跑']\n",
    "for item in sports:\n",
    "    def get_score(x):\n",
    "        for i in range(20):\n",
    "            if x<=score[item]['成绩'].iloc[0]:\n",
    "                if x == 0:\n",
    "                    return 0\n",
    "                else:\n",
    "                    return score[item]['分数'].iloc[0]\n",
    "            elif (x > score[item]['成绩'].iloc[i]) & (x <= score[item]['成绩'].iloc[i+1]): \n",
    "                return score[item]['分数'].iloc[i+1]\n",
    "            elif x > score[item]['成绩'].iloc[-1]:\n",
    "                return 0\n",
    "    \n",
    "    male_table[item+'分数']=male_table[item].transform(get_score) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "id": "24858b74",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.22</td>\n",
       "      <td>8.88</td>\n",
       "      <td>195.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.27</td>\n",
       "      <td>7.70</td>\n",
       "      <td>225.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.15</td>\n",
       "      <td>8.45</td>\n",
       "      <td>218.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.35</td>\n",
       "      <td>8.05</td>\n",
       "      <td>206.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.73</td>\n",
       "      <td>7.52</td>\n",
       "      <td>210.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男50米跑    男跳远  男体前屈  男引体    男肺活量     身高    体重  BMI  男50米跑分数  \\\n",
       "0   1  男     4.22   8.88  195.0  12.0  1.0  2785.0  170.0  72.6  0.0     66.0   \n",
       "1   1  男     4.27   7.70  225.0  11.0  7.0  3133.0  174.0  52.7  0.0     78.0   \n",
       "2   1  男     4.15   8.45  218.0  14.0  1.0  3901.0  169.0  46.5  0.0     70.0   \n",
       "3   1  男     4.35   8.05  206.0  13.0  1.0  4946.0  183.0  79.7  0.0     74.0   \n",
       "4   1  男     3.73   7.52  210.0  13.0  9.0  3538.0  171.0  54.7  0.0     78.0   \n",
       "\n",
       "   男1000米跑分数  男肺活量分数  男跳远分数  男体前屈分数  男引体分数  \n",
       "0       72.0    62.0   60.0    74.0    0.0  \n",
       "1       70.0    68.0   74.0    74.0   78.0  \n",
       "2       74.0    80.0   70.0    78.0    0.0  \n",
       "3       68.0   100.0   64.0    76.0    0.0  \n",
       "4       85.0    74.0   66.0    76.0   78.0  "
      ]
     },
     "execution_count": 218,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "14eb9530",
   "metadata": {},
   "outputs": [],
   "source": [
    "#其它项目成绩对应出分数 ——男\n",
    "sports = ['男肺活量','男跳远','男体前屈','男引体']\n",
    "for item in sports:\n",
    "    def get_score(x):\n",
    "        for i in range(20):\n",
    "            if x>=score[item]['成绩'].iloc[0]:\n",
    "                return score[item]['分数'].iloc[0]\n",
    "            elif (x < score[item]['成绩'].iloc[i]) & (x >= score[item]['成绩'].iloc[i+1]): \n",
    "                return score[item]['分数'].iloc[i+1]\n",
    "            elif x < score[item]['成绩'].iloc[-1]:\n",
    "                return 0\n",
    "    \n",
    "    male_table[item+'分数']=male_table[item].map(get_score) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "id": "80ff1e85",
   "metadata": {},
   "outputs": [],
   "source": [
    "#跑步类项目成绩对应出分数 ——女\n",
    "sports = ['女50米跑','女800米跑']\n",
    "for item in sports:\n",
    "    def get_score(x):\n",
    "        for i in range(20):\n",
    "            if x<=score[item]['成绩'].iloc[0]:\n",
    "                if x == 0:\n",
    "                    return 0\n",
    "                else:\n",
    "                    return score[item]['分数'].iloc[0]\n",
    "            elif (x > score[item]['成绩'].iloc[i]) & (x <= score[item]['成绩'].iloc[i+1]): \n",
    "                return score[item]['分数'].iloc[i+1]\n",
    "            elif x > score[item]['成绩'].iloc[-1]:\n",
    "                return 0\n",
    "    \n",
    "    female_table[item+'分数']=female_table[item].apply(get_score) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "id": "6d647d95",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧分数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>9.32</td>\n",
       "      <td>185.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>11.44</td>\n",
       "      <td>148.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>13.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>9.52</td>\n",
       "      <td>172.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>85.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>9.79</td>\n",
       "      <td>145.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女50米跑    女跳远  女体前屈   女仰卧    女肺活量     身高    体重  BMI  女50米跑分数  \\\n",
       "0   1  女    3.22   9.32  185.0  16.0  48.0  3775.0  163.0  51.3  0.0     72.0   \n",
       "1   1  女    4.59  11.44  148.0   9.0  29.0  3683.0  163.0  66.6  0.0     10.0   \n",
       "2   1  女    3.46  13.40  150.0   7.0  40.0  3331.0  157.0  60.0  0.0      0.0   \n",
       "3   1  女    3.39   9.52  172.0  21.0  46.0  3701.0  160.0  50.7  0.0     70.0   \n",
       "4   1  女    3.43   9.79  145.0   8.0  34.0  3592.0  167.0  63.9  0.0     68.0   \n",
       "\n",
       "   女800米跑分数  女肺活量分数  女跳远分数  女体前屈分数  女仰卧分数  \n",
       "0     100.0   100.0   85.0    76.0   85.0  \n",
       "1      60.0   100.0   60.0    66.0   66.0  \n",
       "2      95.0   100.0   60.0    64.0   76.0  \n",
       "3     100.0   100.0   76.0    90.0   85.0  \n",
       "4      95.0   100.0   50.0    64.0   70.0  "
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "female_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "id": "a02a43d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#其它项目成绩对应出分数 ——女\n",
    "sports = ['女肺活量','女跳远','女体前屈','女仰卧']\n",
    "for item in sports:\n",
    "    def get_score(x):\n",
    "        for i in range(20):\n",
    "            if x>=score[item]['成绩'].iloc[0]:\n",
    "                return score[item]['分数'].iloc[0]\n",
    "            elif (x < score[item]['成绩'].iloc[i]) & (x >= score[item]['成绩'].iloc[i+1]): \n",
    "                return score[item]['分数'].iloc[i+1]\n",
    "            elif x < score[item]['成绩'].iloc[-1]:\n",
    "                return 0\n",
    "    \n",
    "    female_table[item+'分数']=female_table[item].map(get_score) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2cf770c7",
   "metadata": {},
   "source": [
    "### 列索引重排"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "id": "22751abf",
   "metadata": {},
   "outputs": [],
   "source": [
    "sports_score=['男1000米跑分数','男50米跑分数','男跳远分数','男体前屈分数','男引体分数','男肺活量分数']\n",
    "location = 3\n",
    "for item in sports_score:\n",
    "    score = male_table.pop(item)\n",
    "    male_table.insert(loc = location,column=item,value=score)\n",
    "    location +=2    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "id": "426fda7d",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.22</td>\n",
       "      <td>72.0</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66.0</td>\n",
       "      <td>195.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2784.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.62</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.27</td>\n",
       "      <td>70.0</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>3132.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.69</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.15</td>\n",
       "      <td>74.0</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3900.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.50</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.35</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4944.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.69</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.73</td>\n",
       "      <td>85.0</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.69</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  男1000米跑  男1000米跑分数  男50米跑  男50米跑分数    男跳远  男跳远分数  男体前屈  男体前屈分数  男引体  \\\n",
       "0   1  男     4.22       72.0   8.88     66.0  195.0   60.0  12.0    74.0  1.0   \n",
       "1   1  男     4.27       70.0   7.70     78.0  225.0   74.0  11.0    74.0  7.0   \n",
       "2   1  男     4.15       74.0   8.45     70.0  218.0   70.0  14.0    78.0  1.0   \n",
       "3   1  男     4.35       68.0   8.05     74.0  206.0   64.0  13.0    76.0  1.0   \n",
       "4   1  男     3.73       85.0   7.52     78.0  210.0   66.0  13.0    76.0  9.0   \n",
       "\n",
       "   男引体分数    男肺活量  男肺活量分数     身高     体重  BMI  \n",
       "0    0.0  2784.0    62.0  170.0  72.62  0.0  \n",
       "1   78.0  3132.0    68.0  174.0  52.69  0.0  \n",
       "2    0.0  3900.0    80.0  169.0  46.50  0.0  \n",
       "3    0.0  4944.0   100.0  183.0  79.69  0.0  \n",
       "4   78.0  3538.0    74.0  171.0  54.69  0.0  "
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "male_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "id": "74e2baed",
   "metadata": {},
   "outputs": [],
   "source": [
    "sports_score=['女800米跑分数','女50米跑分数','女跳远分数','女体前屈分数','女仰卧分数','女肺活量分数']\n",
    "location = 3\n",
    "for item in sports_score:\n",
    "    score = female_table.pop(item)\n",
    "    female_table.insert(loc = location,column=item,value=score)\n",
    "    location +=2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "id": "8fe632a6",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>60.0</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>95.0</td>\n",
       "      <td>13.40</td>\n",
       "      <td>0.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>95.0</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   班级 性别  女800米跑  女800米跑分数  女50米跑  女50米跑分数    女跳远  女跳远分数  女体前屈  女体前屈分数   女仰卧  \\\n",
       "0   1  女    3.22     100.0   9.32     72.0  185.0   85.0  16.0    76.0  48.0   \n",
       "1   1  女    4.59      60.0  11.44     10.0  148.0   60.0   9.0    66.0  29.0   \n",
       "2   1  女    3.46      95.0  13.40      0.0  150.0   60.0   7.0    64.0  40.0   \n",
       "3   1  女    3.39     100.0   9.52     70.0  172.0   76.0  21.0    90.0  46.0   \n",
       "4   1  女    3.43      95.0   9.79     68.0  145.0   50.0   8.0    64.0  34.0   \n",
       "\n",
       "   女仰卧分数    女肺活量  女肺活量分数     身高    体重  BMI  \n",
       "0   85.0  3775.0   100.0  163.0  51.3  0.0  \n",
       "1   66.0  3683.0   100.0  163.0  66.6  0.0  \n",
       "2   76.0  3331.0   100.0  157.0  60.0  0.0  \n",
       "3   85.0  3701.0   100.0  160.0  50.7  0.0  \n",
       "4   70.0  3592.0   100.0  167.0  63.9  0.0  "
      ]
     },
     "execution_count": 194,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "female_table.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "id": "15afd3f6",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter('./sports_result.xlsx') as writer:\n",
    "    male_table.to_excel(writer,sheet_name=\"male\",header=True,index=False)\n",
    "    female_table.to_excel(writer,sheet_name=\"female\",header=True,index=False)    #整理后成绩表导出为Excel文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "60973a87",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
